package com.waypolice.common.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
 * @Description
 * @Author wangrang
 * @Date Created in 15:12 2019/3/15
 * @Modified By:
 */
public class ReadExcelUtils {
    private static final Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);

    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public ReadExcelUtils(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    public ReadExcelUtils(InputStream inputStream,String fileName) {
        if(inputStream==null){
            return;
        }
        try {
            String strSuffix = fileName.split("\\.")[1];
            if("xls".equals(strSuffix)){
                wb = new HSSFWorkbook(inputStream);
            }else if("xlsx".equals(strSuffix)){
                wb = new XSSFWorkbook(inputStream);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    /**
     * 读取Excel表格表头的内容
     *
     * @return String 表头内容的数组
     * @author zengwendong
     */
    public String[] readExcelTitle() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            // title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = row.getCell(i).getCellFormula();
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     * @author form 第几个表格
     */
    public List<Map<String,Object>> readExcelContent(Integer form) throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
       // Map<Integer, Map<String,Object>> content = new HashMap<Integer, Map<String,Object>>();
        List<Map<String,Object>> content=new ArrayList<>();
        sheet = wb.getSheetAt(form);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(1);
        int colNum = row.getPhysicalNumberOfCells();

        // 正文内容应该从第二行开始,第一行为表头的标题
        ArrayList<String> fieldName = new ArrayList<>();
        for (int i = 1; i <= rowNum; i++) {
            Map<String,Object> cellValue = new HashMap<>();
            if(i==1){
                row = sheet.getRow(i);
                int j1 = 0;
                while (j1 < colNum) {
                    Object obj = getCellFormatValue(row.getCell(j1));
                    fieldName.add(obj.toString());
                    j1++;
                }
            }else {
                row = sheet.getRow(i);
                //去掉ID为空的
                Object cellFormatValue = getCellFormatValue(row.getCell(0));
                /*if(StringUtils.isNotBlank(cellFormatValue.toString())){*/
                    int j = 0;
                    while (j < colNum) {
                        Object obj = getCellFormatValue(row.getCell(j));
                        if(StringUtils.isNotBlank(obj.toString())){
                            cellValue.put(fieldName.get(j), obj);
                        }
                        j++;
                    }
                    //content.put(i, cellValue);
                    content.add(cellValue);
               /* }*/
            }
        }
        return content;
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     * @author zengwendong
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_NUMERIC:
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        // data格式是带时分秒的：2013-7-10 0:00:00
                        // cellvalue = cell.getDateCellValue().toLocaleString();
                        // data格式是不带带时分秒的：2013-7-10;
                        Date date = cell.getDateCellValue();
                        cellvalue = date;
                    } else {// 如果是纯数字
                        //先转换为文本格式 在重新应用
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                        }
                        // 取得当前Cell的数值
                        cellvalue = cell.getStringCellValue();
                    }
                    break;
                }
                // 如果当前Cell的Type为STRING
                case Cell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:// 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

    public static void main(String[] args) {
        try {
            String filepath = "F:test.xls";
            ReadExcelUtils excelReader = new ReadExcelUtils(filepath);
            // 对读取Excel表格标题测试
//          String[] title = excelReader.readExcelTitle();
//          System.out.println("获得Excel表格的标题:");
//          for (String s : title) {
//              System.out.print(s + " ");
//          }

            // 对读取Excel表格内容测试
            List<Map<String, Object>> maps = excelReader.readExcelContent(1);
            System.out.println("获得Excel表格的内容:");
            for (int i = 0; i < maps.size(); i++) {
                System.out.println("第1张表:"+maps.get(i));
            }


           /* Map<Integer, Map<String,Object>> map2 = excelReader.readExcelContent(1);
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map2.size(); i++) {
                System.out.println("第二张表:"+map2.get(i));
            }*/
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}
